const { Op, Sequelize } = require('sequelize');
const { User, PointsTransaction, Order, OrderItem, Product, AdView } = require('../models');
const moment = require('moment');

class BusinessAnalyticsService {
  /**
   * 获取积分经济分析数据
   */
  async getPointsEconomyAnalysis(startDate, endDate) {
    try {
      const start = moment(startDate).startOf('day').toDate();
      const end = moment(endDate).endOf('day').toDate();

      // 积分发放统计
      const pointsIssued = await PointRecord.findAll({
        where: {
          type: 'earn',
          created_at: { [Op.between]: [start, end] }
        },
        attributes: [
          'source',
          [Sequelize.fn('SUM', Sequelize.col('amount')), 'total_amount'],
          [Sequelize.fn('COUNT', Sequelize.col('id')), 'count']
        ],
        group: ['source']
      });

      // 积分消费统计
      const pointsConsumed = await PointRecord.findAll({
        where: {
          type: 'consume',
          created_at: { [Op.between]: [start, end] }
        },
        attributes: [
          [Sequelize.fn('SUM', Sequelize.col('amount')), 'total_amount'],
          [Sequelize.fn('COUNT', Sequelize.col('id')), 'count']
        ]
      });

      // 积分余额分布
      const pointsDistribution = await User.findAll({
        attributes: [
          [Sequelize.literal('CASE 
            WHEN points = 0 THEN "0"
            WHEN points <= 100 THEN "1-100"
            WHEN points <= 500 THEN "101-500"
            WHEN points <= 1000 THEN "501-1000"
            ELSE "1000+"
          END'), 'range'],
          [Sequelize.fn('COUNT', Sequelize.col('id')), 'user_count']
        ],
        group: [Sequelize.literal('CASE 
          WHEN points = 0 THEN "0"
          WHEN points <= 100 THEN "1-100"
          WHEN points <= 500 THEN "101-500"
          WHEN points <= 1000 THEN "501-1000"
          ELSE "1000+"
        END')]
      });

      // 积分流转趋势（按天）
      const pointsFlow = await PointRecord.findAll({
        where: {
          created_at: { [Op.between]: [start, end] }
        },
        attributes: [
          [Sequelize.fn('DATE', Sequelize.col('created_at')), 'date'],
          'type',
          [Sequelize.fn('SUM', Sequelize.col('amount')), 'total_amount']
        ],
        group: [
          Sequelize.fn('DATE', Sequelize.col('created_at')),
          'type'
        ],
        order: [[Sequelize.fn('DATE', Sequelize.col('created_at')), 'ASC']]
      });

      return {
        pointsIssued: pointsIssued.map(item => ({
          source: item.source,
          totalAmount: parseFloat(item.dataValues.total_amount || 0),
          count: parseInt(item.dataValues.count || 0)
        })),
        pointsConsumed: {
          totalAmount: parseFloat(pointsConsumed[0]?.dataValues.total_amount || 0),
          count: parseInt(pointsConsumed[0]?.dataValues.count || 0)
        },
        pointsDistribution: pointsDistribution.map(item => ({
          range: item.dataValues.range,
          userCount: parseInt(item.dataValues.user_count)
        })),
        pointsFlow: pointsFlow.map(item => ({
          date: item.dataValues.date,
          type: item.type,
          amount: parseFloat(item.dataValues.total_amount)
        }))
      };
    } catch (error) {
      console.error('获取积分经济分析失败:', error);
      throw new Error('获取积分经济分析失败');
    }
  }

  /**
   * 获取广告效果和收益分析
   */
  async getAdEffectAnalysis(startDate, endDate) {
    try {
      const start = moment(startDate).startOf('day').toDate();
      const end = moment(endDate).endOf('day').toDate();

      // 广告观看统计
      const adViewStats = await AdView.findAll({
        where: {
          created_at: { [Op.between]: [start, end] }
        },
        attributes: [
          'ad_type',
          'platform',
          [Sequelize.fn('COUNT', Sequelize.col('id')), 'view_count'],
          [Sequelize.fn('SUM', Sequelize.literal('CASE WHEN is_completed = 1 THEN 1 ELSE 0 END')), 'completed_count'],
          [Sequelize.fn('AVG', Sequelize.col('view_duration')), 'avg_duration']
        ],
        group: ['ad_type', 'platform']
      });

      // 广告收益统计（基于积分发放）
      const adRevenue = await PointRecord.findAll({
        where: {
          source: 'ad_watch',
          created_at: { [Op.between]: [start, end] }
        },
        attributes: [
          [Sequelize.fn('SUM', Sequelize.col('amount')), 'total_points'],
          [Sequelize.fn('COUNT', Sequelize.col('id')), 'reward_count']
        ]
      });

      // 广告效果趋势
      const adTrend = await AdView.findAll({
        where: {
          created_at: { [Op.between]: [start, end] }
        },
        attributes: [
          [Sequelize.fn('DATE', Sequelize.col('created_at')), 'date'],
          [Sequelize.fn('COUNT', Sequelize.col('id')), 'view_count'],
          [Sequelize.fn('SUM', Sequelize.literal('CASE WHEN is_completed = 1 THEN 1 ELSE 0 END')), 'completed_count']
        ],
        group: [Sequelize.fn('DATE', Sequelize.col('created_at'))],
        order: [[Sequelize.fn('DATE', Sequelize.col('created_at')), 'ASC']]
      });

      // 用户广告观看行为分析
      const userAdBehavior = await AdView.findAll({
        where: {
          created_at: { [Op.between]: [start, end] }
        },
        attributes: [
          'user_id',
          [Sequelize.fn('COUNT', Sequelize.col('id')), 'view_count'],
          [Sequelize.fn('SUM', Sequelize.literal('CASE WHEN is_completed = 1 THEN 1 ELSE 0 END')), 'completed_count']
        ],
        group: ['user_id'],
        having: Sequelize.literal('view_count > 0'),
        order: [[Sequelize.literal('view_count'), 'DESC']],
        limit: 100
      });

      return {
        adViewStats: adViewStats.map(item => ({
          adType: item.ad_type,
          platform: item.platform,
          viewCount: parseInt(item.dataValues.view_count),
          completedCount: parseInt(item.dataValues.completed_count || 0),
          completionRate: item.dataValues.view_count > 0 ? 
            (parseInt(item.dataValues.completed_count || 0) / parseInt(item.dataValues.view_count) * 100).toFixed(2) : 0,
          avgDuration: parseFloat(item.dataValues.avg_duration || 0).toFixed(2)
        })),
        adRevenue: {
          totalPoints: parseFloat(adRevenue[0]?.dataValues.total_points || 0),
          rewardCount: parseInt(adRevenue[0]?.dataValues.reward_count || 0)
        },
        adTrend: adTrend.map(item => ({
          date: item.dataValues.date,
          viewCount: parseInt(item.dataValues.view_count),
          completedCount: parseInt(item.dataValues.completed_count || 0),
          completionRate: item.dataValues.view_count > 0 ? 
            (parseInt(item.dataValues.completed_count || 0) / parseInt(item.dataValues.view_count) * 100).toFixed(2) : 0
        })),
        topUsers: userAdBehavior.map(item => ({
          userId: item.user_id,
          viewCount: parseInt(item.dataValues.view_count),
          completedCount: parseInt(item.dataValues.completed_count || 0)
        }))
      };
    } catch (error) {
      console.error('获取广告效果分析失败:', error);
      throw new Error('获取广告效果分析失败');
    }
  }

  /**
   * 获取商品销售和趋势分析
   */
  async getProductSalesAnalysis(startDate, endDate) {
    try {
      const start = moment(startDate).startOf('day').toDate();
      const end = moment(endDate).endOf('day').toDate();

      // 商品销售统计
      const productSales = await OrderItem.findAll({
        include: [
          {
            model: Order,
            where: {
              status: 'completed',
              created_at: { [Op.between]: [start, end] }
            },
            attributes: []
          },
          {
            model: Product,
            attributes: ['name', 'category', 'type']
          }
        ],
        attributes: [
          'product_id',
          [Sequelize.fn('SUM', Sequelize.col('quantity')), 'total_quantity'],
          [Sequelize.fn('SUM', Sequelize.col('total_points')), 'total_points'],
          [Sequelize.fn('COUNT', Sequelize.col('OrderItem.id')), 'order_count']
        ],
        group: ['product_id', 'Product.id'],
        order: [[Sequelize.literal('total_quantity'), 'DESC']]
      });

      // 商品分类销售统计
      const categorySales = await OrderItem.findAll({
        include: [
          {
            model: Order,
            where: {
              status: 'completed',
              created_at: { [Op.between]: [start, end] }
            },
            attributes: []
          },
          {
            model: Product,
            attributes: ['category']
          }
        ],
        attributes: [
          [Sequelize.col('Product.category'), 'category'],
          [Sequelize.fn('SUM', Sequelize.col('quantity')), 'total_quantity'],
          [Sequelize.fn('SUM', Sequelize.col('total_points')), 'total_points']
        ],
        group: ['Product.category']
      });

      // 销售趋势（按天）
      const salesTrend = await Order.findAll({
        where: {
          status: 'completed',
          created_at: { [Op.between]: [start, end] }
        },
        attributes: [
          [Sequelize.fn('DATE', Sequelize.col('created_at')), 'date'],
          [Sequelize.fn('COUNT', Sequelize.col('id')), 'order_count'],
          [Sequelize.fn('SUM', Sequelize.col('total_points')), 'total_points'],
          [Sequelize.fn('SUM', Sequelize.col('total_quantity')), 'total_quantity']
        ],
        group: [Sequelize.fn('DATE', Sequelize.col('created_at'))],
        order: [[Sequelize.fn('DATE', Sequelize.col('created_at')), 'ASC']]
      });

      // 热销商品排行
      const hotProducts = await OrderItem.findAll({
        include: [
          {
            model: Order,
            where: {
              status: 'completed',
              created_at: { [Op.between]: [start, end] }
            },
            attributes: []
          },
          {
            model: Product,
            attributes: ['name', 'category', 'points_price']
          }
        ],
        attributes: [
          'product_id',
          [Sequelize.fn('SUM', Sequelize.col('quantity')), 'total_sales']
        ],
        group: ['product_id', 'Product.id'],
        order: [[Sequelize.literal('total_sales'), 'DESC']],
        limit: 10
      });

      return {
        productSales: productSales.map(item => ({
          productId: item.product_id,
          productName: item.Product?.name,
          category: item.Product?.category,
          type: item.Product?.type,
          totalQuantity: parseInt(item.dataValues.total_quantity),
          totalPoints: parseFloat(item.dataValues.total_points),
          orderCount: parseInt(item.dataValues.order_count)
        })),
        categorySales: categorySales.map(item => ({
          category: item.dataValues.category,
          totalQuantity: parseInt(item.dataValues.total_quantity),
          totalPoints: parseFloat(item.dataValues.total_points)
        })),
        salesTrend: salesTrend.map(item => ({
          date: item.dataValues.date,
          orderCount: parseInt(item.dataValues.order_count),
          totalPoints: parseFloat(item.dataValues.total_points),
          totalQuantity: parseInt(item.dataValues.total_quantity)
        })),
        hotProducts: hotProducts.map(item => ({
          productId: item.product_id,
          productName: item.Product?.name,
          category: item.Product?.category,
          pointsPrice: parseFloat(item.Product?.points_price || 0),
          totalSales: parseInt(item.dataValues.total_sales)
        }))
      };
    } catch (error) {
      console.error('获取商品销售分析失败:', error);
      throw new Error('获取商品销售分析失败');
    }
  }

  /**
   * 获取财务数据和盈利分析
   */
  async getFinancialAnalysis(startDate, endDate) {
    try {
      const start = moment(startDate).startOf('day').toDate();
      const end = moment(endDate).endOf('day').toDate();

      // 积分成本分析（发放的积分总量）
      const pointsCost = await PointRecord.findAll({
        where: {
          type: 'earn',
          created_at: { [Op.between]: [start, end] }
        },
        attributes: [
          'source',
          [Sequelize.fn('SUM', Sequelize.col('amount')), 'total_cost']
        ],
        group: ['source']
      });

      // 积分收入分析（消费的积分总量）
      const pointsRevenue = await PointRecord.findAll({
        where: {
          type: 'consume',
          created_at: { [Op.between]: [start, end] }
        },
        attributes: [
          [Sequelize.fn('SUM', Sequelize.col('amount')), 'total_revenue']
        ]
      });

      // 用户价值分析
      const userValue = await User.findAll({
        include: [
          {
            model: PointRecord,
            where: {
              type: 'consume',
              created_at: { [Op.between]: [start, end] }
            },
            attributes: [],
            required: false
          }
        ],
        attributes: [
          'id',
          'member_level',
          [Sequelize.fn('SUM', Sequelize.col('PointRecords.amount')), 'total_consumption'],
          [Sequelize.fn('COUNT', Sequelize.col('PointRecords.id')), 'transaction_count']
        ],
        group: ['User.id'],
        having: Sequelize.literal('total_consumption > 0'),
        order: [[Sequelize.literal('total_consumption'), 'DESC']],
        limit: 100
      });

      // 会员等级价值分析
      const memberLevelValue = await User.findAll({
        include: [
          {
            model: PointRecord,
            where: {
              type: 'consume',
              created_at: { [Op.between]: [start, end] }
            },
            attributes: [],
            required: false
          }
        ],
        attributes: [
          'member_level',
          [Sequelize.fn('COUNT', Sequelize.col('User.id')), 'user_count'],
          [Sequelize.fn('AVG', Sequelize.col('PointRecords.amount')), 'avg_consumption'],
          [Sequelize.fn('SUM', Sequelize.col('PointRecords.amount')), 'total_consumption']
        ],
        group: ['member_level']
      });

      // ROI分析（投入产出比）
      const totalCost = pointsCost.reduce((sum, item) => sum + parseFloat(item.dataValues.total_cost || 0), 0);
      const totalRevenue = parseFloat(pointsRevenue[0]?.dataValues.total_revenue || 0);
      const roi = totalCost > 0 ? ((totalRevenue - totalCost) / totalCost * 100).toFixed(2) : 0;

      return {
        pointsCost: pointsCost.map(item => ({
          source: item.source,
          totalCost: parseFloat(item.dataValues.total_cost || 0)
        })),
        pointsRevenue: {
          totalRevenue: totalRevenue
        },
        profitAnalysis: {
          totalCost: totalCost,
          totalRevenue: totalRevenue,
          profit: totalRevenue - totalCost,
          roi: parseFloat(roi)
        },
        userValue: userValue.map(item => ({
          userId: item.id,
          memberLevel: item.member_level,
          totalConsumption: parseFloat(item.dataValues.total_consumption || 0),
          transactionCount: parseInt(item.dataValues.transaction_count || 0)
        })),
        memberLevelValue: memberLevelValue.map(item => ({
          memberLevel: item.member_level,
          userCount: parseInt(item.dataValues.user_count || 0),
          avgConsumption: parseFloat(item.dataValues.avg_consumption || 0),
          totalConsumption: parseFloat(item.dataValues.total_consumption || 0)
        }))
      };
    } catch (error) {
      console.error('获取财务分析失败:', error);
      throw new Error('获取财务分析失败');
    }
  }

  /**
   * 获取综合业务分析报告
   */
  async getComprehensiveAnalysis(startDate, endDate) {
    try {
      const [pointsAnalysis, adAnalysis, salesAnalysis, financialAnalysis] = await Promise.all([
        this.getPointsEconomyAnalysis(startDate, endDate),
        this.getAdEffectAnalysis(startDate, endDate),
        this.getProductSalesAnalysis(startDate, endDate),
        this.getFinancialAnalysis(startDate, endDate)
      ]);

      return {
        period: {
          startDate,
          endDate
        },
        pointsEconomy: pointsAnalysis,
        adEffect: adAnalysis,
        productSales: salesAnalysis,
        financial: financialAnalysis,
        generatedAt: new Date()
      };
    } catch (error) {
      console.error('获取综合业务分析失败:', error);
      throw new Error('获取综合业务分析失败');
    }
  }
}

module.exports = new BusinessAnalyticsService();